﻿using System;
using System.Text;
using System.Collections.Generic;
using bfs.sms.sdmx.util.Variable;
using CarlosAg.ExcelXmlWriter;
using Ch.Admin.Bfs.Sms.Sdmx.Extension.CodeBook.Meta;
using SdmxMl.Common;
namespace bfs.sms.sdmx.util.Excel
{
    /// <summary>
    /// Helper class holding one row of TOC
    /// </summary>
    public class TableOfContentInfo
    {
        public string Text { get; private set; }
        public string SheetId { get; private set; }
        public TableOfContentInfo(string tocSheetId, string tocText)
        {
            Text = tocText;
            SheetId = tocSheetId;
        }
    }
    /// <summary> Base class for Excel export of Catalog </summary>
    public abstract class ExcelBasicCatalog
    {
        #region Constants

        protected const string CELL_STYLE_DEFAULT = "Default";
        protected const string CELL_STYLE_NUM_LEFT = "s11";
        protected const string CELL_STYLE_BOLD = "s12";
        protected const string CELL_STYLE_HEADER = "s22";
        protected const string CELL_STYLE_DESCRIBE = "s23";
        protected const string CELL_STYLE_HLINK = "s62";
        protected const string CELL_STYLE_HLINK_HEADER = "s62H";

        protected const string CELL_COLOR_HEADER = "#C0C0C0";
        protected const string CELL_COLOR_LINK = "#0000FF";

        protected const string QualitySheetId = "S";
        protected const string QualityHeaderName = "Special Codes";
        protected const string FILTER_NAME = "_FilterDatabase";

        #endregion

        #region Internal members

        /// <summary> Catalog book </summary>
        protected Workbook book;
        /// <summary> current sheet in Catalog book </summary>
        protected Worksheet sheet;
        /// <summary>Table content set </summary>
        protected HashSet<TableOfContentInfo> tocSet;
        /// <summary>Table of Contents sheet</summary>
        protected Worksheet sheetToc;
        /// <summary>Language to use for generation</summary>
        protected string langCd;
        /// <summary> Generations options to use</summary>
        protected VarCatalogOptions catalogOptions;

        #endregion

        /// <summary> Only if set to true cell writer will place numeral cell when detected </summary>
        public bool SetNumericCell { get; set; }

        #region C'tor

        /// <summary> Construct Excel book main properties </summary>
        /// <param name="author">Author of the Book</param>
        /// <param name="company">Company owning book</param>
        public ExcelBasicCatalog(string author, string company)
        {
            book = new Workbook();
            tocSet = new HashSet<TableOfContentInfo>();

            // -----------------------------------------------
            //  Properties
            // -----------------------------------------------
            book.Properties.Author = author;
            book.Properties.LastAuthor = author;
            book.Properties.Created = DateTime.Now;
            book.Properties.Company = company;
            book.Properties.Version = "11.9999";
            book.ExcelWorkbook.WindowHeight = 12270;
            book.ExcelWorkbook.WindowWidth = 15195;
            book.ExcelWorkbook.WindowTopX = 480;
            book.ExcelWorkbook.WindowTopY = 15;
            book.ExcelWorkbook.ActiveSheetIndex = 0;
            book.ExcelWorkbook.ProtectWindows = false;
            book.ExcelWorkbook.ProtectStructure = false;

            GenerateStyles();
        }

        /// <summary> Defines styles used for Catalog </summary>
        protected virtual void GenerateStyles()
        {
            WorksheetStyleCollection styles = book.Styles;
            // -----------------------------------------------
            //  Default
            // -----------------------------------------------
            WorksheetStyle style = styles.Add(CELL_STYLE_DEFAULT);
            style.Name = "Normal";
            style.Alignment.Vertical = StyleVerticalAlignment.Bottom;

            style = style = styles.Add(CELL_STYLE_BOLD);
            style.Font.Bold = true;
            // -----------------------------------------------
            //  Header
            // -----------------------------------------------
            style = styles.Add(CELL_STYLE_HEADER);
            style.Font.FontName = "Arial Black";
            style.Interior.Color = CELL_COLOR_HEADER;
            style.Interior.Pattern = StyleInteriorPattern.Solid;
            style.Alignment.Vertical = StyleVerticalAlignment.Top;

            style = styles.Add(CELL_STYLE_DESCRIBE);
            style.Alignment.WrapText = true;
            style.Alignment.Vertical = StyleVerticalAlignment.Center;

            // For numeric, required aligned left
            style = styles.Add(CELL_STYLE_NUM_LEFT);
            style.Alignment.Horizontal = StyleHorizontalAlignment.Left;

            // Hyperlink
            style = styles.Add(CELL_STYLE_HLINK);
            style.Font.FontName = "Arial";
            style.Font.Size = 11;
            style.Font.Color = CELL_COLOR_LINK;
            style.Font.Underline = UnderlineStyle.Single;

            // Header Hyperlink
            style = styles.Add(CELL_STYLE_HLINK_HEADER);
            style.Font.FontName = "Arial Black";
            style.Interior.Color = CELL_COLOR_HEADER;
            style.Interior.Pattern = StyleInteriorPattern.Solid;
            style.Font.Underline = UnderlineStyle.Single;
            style.Alignment.Vertical = StyleVerticalAlignment.Top;
        }

        #endregion

        #region Generation

        /// <summary>
        /// Each Excel document has a first sheet with TOC
        /// </summary>
        protected void CreateTableOfContent()
        {
            sheetToc = sheet = book.Worksheets.Add("TOC");
            sheetToc.Table.DefaultColumnWidth = 70F;
            sheetToc.Table.ExpandedColumnCount = 1;
            sheetToc.Table.ExpandedRowCount = 3;
            sheetToc.Table.FullColumns = 1;
            sheetToc.Table.FullRows = 1;

            // Columns code override size
            WorksheetColumn columnVar = sheetToc.Table.Columns.Add();
            columnVar.Index = 1;
            columnVar.Width = 1000;

            // Lock top rows
            SplitSheetHorizontal(sheetToc, 3);

       }

        /// <summary>
        /// Split rows and lock top rows
        /// </summary>
        /// <param name="sheet">sheet where to operate</param>
        /// <param name="count">count of rows to lock</param>
        protected void SplitSheetHorizontal(Worksheet sheet, int count)
        {
            sheet.Options.SplitHorizontal = count;
            sheet.Options.TopRowBottomPane = count;
            sheet.Options.ActivePane = 2;
            sheet.Options.FreezePanes = true;
        }

        protected void CreateNamedRange(Worksheet sheet, int top, int count, int cols)
        {
            string range = "R" + top.ToString() + "C1:R" + (top + count).ToString() + "C" + cols.ToString();
            WorksheetNamedRange wnr = new WorksheetNamedRange(FILTER_NAME, 
                "=" + sheet.Name + "!" + range, false);

            sheet.Names.Add(wnr);
            sheet.AutoFilter.Range = range;
        }

        protected virtual void FillTableOfContents()
        {
            MultiLang toc = new MultiLang("Table of contents");
            toc.UpdateForLanguage(LangSupport.FRENCH_ID, "Table des matières");
            toc.UpdateForLanguage(LangSupport.GERMAN_ID, "Inhaltsverzeichnis");
            toc.UpdateForLanguage(LangSupport.ITALIAN_ID, "Sommario");

            WorksheetRow row = sheetToc.Table.Rows.Add();
            WorksheetCell cell = AddCell(row, DateTime.Now.ToString());
            row = sheetToc.Table.Rows.Add();
            cell = AddCell(row, string.Empty);
            row = sheetToc.Table.Rows.Add();
            cell = AddCell(row, toc.ToString(), CELL_STYLE_HEADER);
            
            sheetToc.Table.ExpandedRowCount = 3 + tocSet.Count;

            foreach (TableOfContentInfo tocInfo in tocSet)
            {
                row = sheetToc.Table.Rows.Add();
                AddCellSheetlink(row, tocInfo.Text, tocInfo.SheetId, null);
            }
            


        }

        /// <summary> Excel XML Workbook generation </summary>
        /// <param name="varSheets"></param>
        /// <param name="lang">language code to use</param>
        public virtual void Generate(List<VariableSheet> varSheets, string lang, VarCatalogOptions options)
        {
            langCd = lang;
            catalogOptions = options;
            CreateTableOfContent();
            foreach (VariableSheet vs in varSheets)
                GenerateWorksheet(vs);
            FillTableOfContents();
        }

        /// <summary>
        /// Generation of one worksheet of the book
        /// </summary>
        /// <param name="varSheet"></param>
        protected virtual void GenerateWorksheet(VariableSheet varSheet)
        {
        }

        /// <summary>
        /// Add artefact identification and description, add columns header
        /// </summary>
        /// <param name="id">Artefact identification</param>
        /// <param name="name">artefact name</param>
        /// <param name="descr">artefact description</param>
        /// <param name="columnHeader">column name list</param>
        protected WorksheetRow AddHeaderInfo(string id, string name, string descr, List<string> columnHeader, bool hasFilter)
        {
            // Codelist ID, Name
            WorksheetRow RowCodelist = sheet.Table.Rows.Add();
            AddCell(RowCodelist, id);
            AddCell(RowCodelist, name, CELL_STYLE_BOLD);

            // Row Description
            WorksheetRow RowDescription = sheet.Table.Rows.Add();
            RowDescription.Height = 60;
            AddCell(RowDescription, string.Empty);
            AddCell(RowDescription, descr, CELL_STYLE_DESCRIBE);

            //Header of columns
            WorksheetRow RowHead = sheet.Table.Rows.Add();
            RowHead.Height = 15;
            foreach (string hc in columnHeader)
                AddCell(RowHead, hc, CELL_STYLE_HEADER, hasFilter);

            return RowHead;
        }

        /// <summary>Helper adding a cell content to the specified row with the default style</summary>
        /// <param name="row">The sheet row</param>
        /// <param name="content">the cell content</param>
        protected WorksheetCell AddCell(WorksheetRow row, string content)
        {
            return AddCell(row, content, string.Empty, false);
        }
        /// <summary>Helper adding a cell content to the specified row with the default style</summary>
        /// <param name="row">The sheet row</param>
        /// <param name="content">the cell content</param>
        protected WorksheetCell AddCell(WorksheetRow row, string content, bool hasFilter)
        {
            return AddCell(row, content, string.Empty, hasFilter);
        }
        protected WorksheetCell AddCell(WorksheetRow row, string content, string style)
        {
            return AddCell(row, content, style, false);
        }

        /// <summary>Helper adding a cell content to the specified row</summary>
        /// <param name="row">The sheet row</param>
        /// <param name="content">the cell content</param>
        /// <param name="style">the style id to use for cell</param>
        /// <returns></returns>
        protected WorksheetCell AddCell(WorksheetRow row, string content, string style, bool hasFilter)
        {
            WorksheetCell cell;
            cell = row.Cells.Add();
            if (hasFilter)
                cell.NamedCell.Add(FILTER_NAME);

            int n;
            bool isNumeric = int.TryParse(content, out n);

            // Not numeric if cells numeric but start with 0 and longer than one char
            if (isNumeric && SetNumericCell == false)
                isNumeric = false;
            cell.Data.Type = isNumeric ? DataType.Number : DataType.String;
            cell.Data.Text = content;
            if (style.Length > 0)
                cell.StyleID = style;
            else if (isNumeric)
                cell.StyleID = CELL_STYLE_NUM_LEFT;
            return cell;
        }
        protected WorksheetCell AddCellHyperlink(WorksheetRow row, string path, string bookName, string text, string sheetName, string cellCode, string styleId)
        {
            return AddCellHyperlink(row, path, bookName, text, sheetName, cellCode, styleId, false);
        }

        protected WorksheetCell AddCellHyperlink(WorksheetRow row, string path, string bookName, string text, string sheetName, string cellCode, string styleId, bool hasFilter)
        {
            WorksheetCell cell;
            cell = row.Cells.Add();
            int n;
            bool isNumeric = int.TryParse(text, out n);
            cell.Data.Type = isNumeric ? DataType.Number : DataType.String;
            cell.Data.Text = text;
            if (hasFilter)
                cell.NamedCell.Add(FILTER_NAME);

            if (string.IsNullOrEmpty(bookName) == false  && string.IsNullOrEmpty(text) == false)
            {
                //    <Cell ss:StyleID="s65" ss:Formula="=HYPERLINK(&quot;[.\CODES_TestIT.xls]1!A1&quot;,&quot;1&quot;)"
                StringBuilder sb = new StringBuilder("=HYPERLINK(\"");
                sb.Append("[").Append(bookName).Append("]");
                if (string.IsNullOrEmpty(sheetName) == false)
                {
                    sb.Append(sheetName);
                    if (string.IsNullOrEmpty(cellCode) == false)
                        sb.Append("!").Append(cellCode);
                }
                sb.Append("\",\"").Append(text).Append("\")");
                cell.StyleID = styleId;
                cell.Formula = sb.ToString();
            }

            return cell;
        }
        protected WorksheetCell AddCellSheetlink(WorksheetRow row, string text, string sheetName, string styleId)
        {
            WorksheetCell cell;
            cell = row.Cells.Add();
            cell.Data.Type = DataType.String;
            cell.Data.Text = text;
            if (string.IsNullOrEmpty(styleId) == false)
                cell.StyleID = styleId;

            if (string.IsNullOrEmpty(sheetName) == false)
            {
                cell.HRef = "#" + sheetName + "!A1";
            }

            return cell;
        }

        #endregion

        #region Excel writing

        public void Save(string path)
        {
            if (book != null)
                book.Save(path);

     //       System.Threading.Thread.Sleep(800);
        }
        #endregion

      }
}
